package com.zxy.supplier_system.server.repository

import com.zxy.supplier_system.server.entity.GeneralGoodsCategory
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Query

interface GeneralGoodsCategoryRepository : JpaRepository<GeneralGoodsCategory, Int> {
    
    @Query("""
        WITH RECURSIVE category_path AS (
          SELECT id, name, parent_id
          FROM general_goods_category
          WHERE id IN :leafIds
          UNION
          SELECT c.id, c.name, c.parent_id
          FROM general_goods_category c
          JOIN category_path cp ON c.id = cp.parent_id
        )
        SELECT * FROM category_path
    """, nativeQuery = true)
    fun findCategoriesByLeafIds(leafIds: List<Int>): List<GeneralGoodsCategory>
}